package com.datawarehouse.dw_utils.controller;

import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.util.List;

/**
 * oracle表结构导出
 *
 * @author zcc ON 2019/1/13
 **/
@RestController
public class OracleSchemaController {
    @GetMapping("/oracle_download")
    public String downloadFile(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String sql = "SELECT B.TABLE_NAME AS '表名',C.COMMENTS AS '表说明',B.COLUMN_ID AS '字段序号',B.COLUMN_NAME AS '字段名',B.DATA_TYPE AS '字段数据类型',B.DATA_LENGTH AS '数据长度',A.COMMENTS AS '字段说明'\n" +
        "FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C\n" +
        "WHERE A.OWNER = 'BIZ' AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND C.TABLE_NAME = A.TABLE_NAME AND C.OWNER = A.OWNER AND C.TABLE_TYPE = ?\n" +
        "ORDER BY A.TABLE_NAME, B.COLUMN_ID";
        DataSource ds = DSFactory.get("ds_oracle");
        List<Entity> rs = Db.use(ds).query(sql,"TABLE");
        // 写出数据到本地
        // ExcelWriter writer = ExcelUtil.getWriter("F:/writeMapTest.xlsx");
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 一次性写出内容，使用默认样式
        writer.write(rs);
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=oracleSchema.xlsx");
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out);
        // 关闭writer与out，释放内存
        out.close();
        writer.close();
        // 关闭数据库连接
        DbUtil.close();
        return "下载成功";
    }
}
